package com.cicadamob.contact.api.service;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build;
import com.cicadamob.contact.api.Utils.ContactConfig;
import com.cicadamob.contact.api.Utils.StringUtil;

/**
 * 
* @ClassName: ContactDbHelper
* @Description: 集团通讯录db处理类
* @author liyezhen
* @date 2015年9月10日 下午4:04:36
*
 */

public class ContactDbHelper
{
	private static String DATABASE = "";
	private static final String COLUMNS = "serverId,pid,name,hasParent,hasChild,level";
	private static final String[] SELECTCOLUMNS = {"serverId", "pid", "name", "hasParent", "hasChild", "level","shareflg"};
	private static final String[] NOSHARE_SELECTCOLUMNS = {"serverId", "pid", "name", "hasParent", "hasChild", "level"};
	private static final String AYSNCOLUMNS = "id,seq,timestamp";
	private static final String AYSN_INFO_TABLE = "sync_info";
//	private static final String[] selections = {"name","number","orgid","vid","shortNumber","vtype","card","telephoneno","jobprofession","address","email","birthday","faxno","qq","msn","mobile2","imgpath"};
	private static final String[] selectionsforcompany = {"name", "jobprofession", "number", "orgid" , "unid", "install"};
	private static final String[] selectionsforclient = {"unid","name", "jobprofession", "number", "orgid", "nickname"};
//	private static final String[] selectionsforcalllog = {"name","vid","shortNumber"};
//	private static final String[] selectionsatcall = {"name","jobprofession,orgid","number"};
	private static final String[] PHRASE_TYPE_SELECTIONS = {"unid", "names", "img", "status", "orders"};
	private static final String[] PHRASE_SELECTIONS = {"type_id", "contents", "status"};
	private static ContactDbHelper ContactDbHelper = null;
	
	protected SQLiteDatabase readableDatabase;
	
	//数据库名称
	public static final String DBFILE = "vcc.db3";
	//精彩短语类型表的表名
	private static final String PHRASE_TYPE_TABLE = "tb_phrase_type";
	//精彩短语表名
	private static final String PHRASE_TABLE = "tb_phrase";
	//同事通讯录表名
	public static final String COMPANY_PERSON_DATABASE = "company_person";
	//同事通讯录组织表的表名
	public static final String COMPANY_TREE_DATABASE = "company_tree";
	//客户通讯录的组织表名
	public static final String COMPANY_CLIENT_TREE_TABLE = "client_tree";
	public static final String TS_GROUP_NAME="TS_GROUP_NAME";
	public static final String TS_GROUP_USER="TS_GROUP_USER";
	public static final String COMPANY_CLIENT_PERSON_TABLE = "client_person";
	public static final String ORDERBY_tree = " oid ASC ";
	public static final String ORDERBY_person = " sid ASC  ";
	public static final String SHAREDPREFERENCES_CHILDORGSIDS_KEY = "childorgsidskey";
	public static final String SHAREDPREFERENCES_CHILDORGSIDS_VALUE = "childorgsidsvalue";
	public static final String newSelections_simple = "name,number,jobprofession,vid,shortNumber,imgpath";
	public static final String CLIENT_PERSON_COLUMNS = "name,number,jobprofession,imgpath,orgid,nickname,shareflg";
	public static final String COMPANY_PERSON_COLUMNS = "name,number,jobprofession,imgpath,orgid,firstpy,allpy,install";
	public static final String GROUP_PERSON_COLUMNS="GROUP_ID,USERTYPE,DISPLAYNAME,USER_ACCOUNT,USER_STATUS,CREATE_DATE,CREATE_USER,GU_REMARK,ISAUT,SPNAME,QPNAME,ISJTUSER,COMPANY,DEPARTMENT,JOBPROFESSION,FNAME_FPY_NUM";
	
	 private AtomicInteger dbOpenCount = new AtomicInteger();//计数器
	 
	public static void reloadDataBase()
	{
		try {
			DATABASE = "";
			File file = ContactConfig.getContext().getFileStreamPath( DBFILE );
			if(file.exists())
				DATABASE = file.getAbsolutePath();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	public void setDataBaseIsNull() {
//		closeDatabase();
		DATABASE = "";
		ContactDbHelper = null;
	}
	
	public void reloadDB() {
//		closeDatabase();
		 if(readableDatabase != null){
			  readableDatabase.close();
		  }
		  readableDatabase = null;
		  dbOpenCount = new AtomicInteger();
	}
	
	@SuppressLint("NewApi")
	public synchronized SQLiteDatabase openDatabase() {
		  if (dbOpenCount.incrementAndGet() == 1) {
			  readableDatabase = openDataBase(false);
			  
			//add by liyezhen 2015.9.10  解决企业通讯录刚下载成功时快速切换主界面与通讯录界面照成数据显示不出来
			  if(readableDatabase == null){
					closeDatabase();
				}
			  
		   if (readableDatabase!=null && Build.VERSION.SDK_INT >= 11) {
			   readableDatabase.enableWriteAheadLogging();// 允许读写同时进行
		   }
		  }
		  return readableDatabase;
	}
	
	 public synchronized void closeDatabase() {
		  if (dbOpenCount.decrementAndGet() == 0) {
			  if(readableDatabase != null){
				  readableDatabase.close();
			  }
			  readableDatabase = null;
		  }
	}
	/**检查指定的数据库文字是否存在*/
	public static  boolean canOpen( String path )
	{
		try
		{
			SQLiteDatabase database = SQLiteDatabase.openDatabase( path, null,
				SQLiteDatabase.OPEN_READONLY );
			database.close();
			return true;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
	}
	
	
	private synchronized SQLiteDatabase openDataBase(boolean readOnly)
	{
		//update by liyezhen 2015.9.10  解决企业通讯录刚下载成功时快速切换主界面与通讯录界面照成数据显示不出来
		if(StringUtil.isEmpty( DATABASE )){
			reloadDataBase();
			if(StringUtil.isEmpty( DATABASE )){
				
				return null;
			}
		}
		
		try
		{
			int flags = SQLiteDatabase.OPEN_READONLY;
			if(!readOnly){
				flags = SQLiteDatabase.OPEN_READWRITE;
			}
			return SQLiteDatabase.openDatabase( DATABASE, null, flags );
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}
	
	private SQLiteDatabase getReadableDatabase()
	{
//		if(readableDatabase == null || !readableDatabase.isOpen())
//			readableDatabase = openDataBase();
		return openDatabase();
	}
	
	public synchronized boolean canOpen()
	{
		SQLiteDatabase database = openDataBase(true);
		if(database == null){
			return false;
		}else{
			database.close();
		}
		return true;
	}
	
	public static ContactDbHelper getInstance(){
		if(ContactDbHelper == null)
			ContactDbHelper = new ContactDbHelper();
//		if( StringUtil.isEmpty( DATABASE ) )
//			reloadDataBase();
		return ContactDbHelper;
	}
	
	private ContactDbHelper() 
	{
		reloadDataBase();
	}
	
	public int getVersion(){
		SQLiteDatabase database = getReadableDatabase();
		if(database == null)
			return 0;
		int version = readableDatabase.getVersion();
		closeDatabase();
		return version;
	}
	
	//获取同步版本信息
	public String[] getAysnInfo() {
		SQLiteDatabase database = getReadableDatabase();
		String[] info = new String[5];
		if(null == database) {
			return null;
		}
		//获取同事时间戳
		String sql = StringUtil.getBuffer().append("select ").append(AYSNCOLUMNS).append(" from ").append(AYSN_INFO_TABLE).append(" where type = 0 ").append(" order by timestamp desc").toString();
		Cursor cursor = null;
		try {
			cursor = database.rawQuery(sql, null);
			if(null != cursor && cursor.getCount() > 0) {
				if(cursor.moveToFirst()) {
					info[0] = StringUtil.isEmpty(cursor.getString(0))?"":cursor.getString(0);
					info[1] = StringUtil.isEmpty(cursor.getString(1))?"":cursor.getString(1);
					info[2] = StringUtil.isEmpty(cursor.getString(2))?"":cursor.getString(2);
				}
			}
			//获取客户时间戳
			sql = StringUtil.getBuffer().append("select ").append(AYSNCOLUMNS).append(" from ").append(AYSN_INFO_TABLE).append(" where type = 1 ").append(" order by timestamp desc").toString();
				cursor = database.rawQuery(sql, null);
				if(null != cursor && cursor.getCount() > 0) {
					if(cursor.moveToFirst()) {
						info[3] = StringUtil.isEmpty(cursor.getString(0))?"":cursor.getString(0);
//						info[1] = StringUtil.isNotNullOrEmpty(cursor.getString(1))?cursor.getString(1):"";
						info[4] = StringUtil.isEmpty(cursor.getString(2))?"":cursor.getString(2);
					}
				}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			
				if(cursor!=null && cursor.isClosed()) {
					cursor.close();
				}
				closeDatabase();
		}
		return info;
	}
	/**
	 * 
	 * @return
	 */
	public String[] getUserCompayInfo(String number) {
		SQLiteDatabase database = getReadableDatabase();
		String[] info = new String[4];
		if(null == database) {
			return null;
		}
		//获取同事时间戳
		String sql = StringUtil.getBuffer().append("select t.name dept ,p.name, t.companypath,p.jobprofession  from ").append(COMPANY_PERSON_DATABASE +" p,"+COMPANY_TREE_DATABASE+" t")
				.append(" where p.orgid=t.serverId and p.number=? ").toString();
		Cursor cursor = null;
		try {
			cursor = database.rawQuery(sql, new String[]{String.valueOf(number)});
			if(null != cursor && cursor.getCount() > 0) {
				if(cursor.moveToFirst()) {
					info[0] = StringUtil.isEmpty(cursor.getString(0))?"":cursor.getString(0);
					info[1] = StringUtil.isEmpty(cursor.getString(1))?"":cursor.getString(1); 
					info[2] = StringUtil.isEmpty(cursor.getString(2))?"":cursor.getString(2);
					info[3] = StringUtil.isEmpty(cursor.getString(3))?"":cursor.getString(3); 
				}
			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
				if(cursor!=null && cursor.isClosed()) {
					cursor.close();
				}
				closeDatabase();
		}
		return info;
	}
	
	/**
	 * 获取短语分类
	 * @return
	 */
	public ArrayList<PhraseType> getPhraseType() {
		SQLiteDatabase database = getReadableDatabase();
		if(null == database) {
			return null;
		}
		ArrayList<PhraseType> typeList = new ArrayList<PhraseType>();
		Cursor cursor = null;
		Cursor countCursor = null;
		try {
			cursor = database.query(PHRASE_TYPE_TABLE, PHRASE_TYPE_SELECTIONS, null, null, null, null, null);
			while (cursor.moveToNext()) {
				int unid = cursor.getInt(0);
				String name = cursor.getString(1);
				String img = cursor.getString(2);
				String status = cursor.getString(3);
				int order = cursor.getInt(3);
				int count = 0;
				countCursor = database.rawQuery("SELECT * FROM " + PHRASE_TABLE +" WHERE type_id=?", new String[]{String.valueOf(unid)});
				while (countCursor.moveToNext()) {
					count = countCursor.getCount();
				}
				PhraseType type = new PhraseType(unid, name, img, status, order, count);
				typeList.add(type);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			
			if(cursor != null && !cursor.isClosed())
				cursor.close();
			if(countCursor != null && !countCursor.isClosed())
				countCursor.close();
			closeDatabase();
		}
		return typeList;
	}
	
	/**
	 * 获取短语
	 * @return
	 */
	public ArrayList<Phrase> getPhrases(int typeId) {
		SQLiteDatabase database = getReadableDatabase();
		if(null == database) {
			return null;
		}
		ArrayList<Phrase> phraseList = new ArrayList<Phrase>();
		Cursor cursor = null;
		try {
			cursor = database.query(PHRASE_TABLE, PHRASE_SELECTIONS, "type_id=" + String.valueOf(typeId), null, null, null, null);
			while (cursor.moveToNext()) {
				String content = cursor.getString(cursor.getColumnIndex("contents"));
				String status = cursor.getString(cursor.getColumnIndex("status"));
				Phrase phrase = new Phrase(typeId, content, status);
				phraseList.add(phrase);
			}
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			if(cursor != null && !cursor.isClosed())
				cursor.close();
			closeDatabase();
		}
		return phraseList;
	}
	
	/**
	 * 获取客户企业信息
	 * @return
	 */
	public Org getClientInfo(int serverId) {
		SQLiteDatabase database = getReadableDatabase();
		if(null == database) {
			return null;
		}
		Org client = new Org();
		Cursor cursor = null;
		Cursor countCursor = null;
		try {
			cursor = database.rawQuery("SELECT name, companyprofiles, companyaddress, projectinfo, xy FROM " + COMPANY_CLIENT_TREE_TABLE +" WHERE serverId = ?", new String[]{String.valueOf(serverId)});
			while (cursor.moveToNext()) {
				
				String name = cursor.getString(0);
				String profiles = cursor.getString(1);
				String address = cursor.getString(2);
				String projectinfo = cursor.getString(3);
				String xy = cursor.getString(4);
				
				client.setName(name);
				client.setProfiles(profiles);
				client.setAddress(address);
				client.setProjectInfo(projectinfo);
				client.setLocationXY(xy);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(cursor != null && !cursor.isClosed())
				cursor.close();
			if(countCursor != null && !countCursor.isClosed())
				countCursor.close();
			closeDatabase();
		}
		return client;
	}
	
	/**
	 * 更新分享标志
	 * @return
	 */
	public synchronized int updateShareflag(String tel) {
		SQLiteDatabase database = openDatabase();
		int update = 0;
		try {
			ContentValues values = new ContentValues();
			values.put("shareflg", "1");
			update = database.update(COMPANY_CLIENT_PERSON_TABLE, values, "number=" + tel, null);
		} catch (Exception e) {
			e.printStackTrace();
			update= 0;
		}finally{
			closeDatabase();
		}
		
		return update;
	}
	
	
	/**根据手机号码查询指定的表
	 * @param mobile 手机
	 * @param personTable 表名
	 * @param columns  要查询的字段
	 * @return
	 */
	public synchronized ArrayList<Contact> queryAll(String mobile ,String personTable,String columns){
//		if(Const.OFFICEASSISTANT == ServerUrl.ProjectNO){
//			return queryAllByPermissionForSuZhou(mobile);
//		}
		
		SQLiteDatabase database = getReadableDatabase();
		ArrayList<Contact> dataList = null;
		if(database == null)
			return new ArrayList<Contact>(1);

		String where = "";
		String keyEncode = "";
		if ( !StringUtil.isEmpty( mobile ) ) {
			keyEncode = StringUtil.encode( mobile );
			if ( StringUtil.isNum( mobile ) ) {
				where = StringUtil.getBuffer().append(" WHERE number like '%").append(mobile).append("%' or shortNumber like '%").append(mobile).append("%'").toString();
			}
			else if(keyEncode.length() == mobile.length())
			{
				where = StringUtil.getBuffer().append(" WHERE firstpy like '%").append(mobile).append("%' or  allpy like '%").append(mobile).append("%'").toString();
			}
			else
			{
				where = StringUtil.getBuffer().append(" WHERE name like '%").append(mobile).append("%'").toString();
			}
		}
		
		String sql = StringUtil.getBuffer().append("SELECT DISTINCT ").append(columns).append(" FROM ").append(personTable).append(where).append(" ORDER BY ").append(ORDERBY_person).toString();
		Cursor cursor = null;
		try
		{
			cursor = database.rawQuery( sql, null );
			if ( cursor != null ){
				int count = cursor.getCount();
				dataList = new ArrayList<Contact>(count*110/100+5);
				while ( cursor.moveToNext() ) {
					Contact contact=new Contact();
//					Contact Contact = new Contact(IsExistColumns(cursor.getColumnIndex("name"))?cursor.getString(cursor.getColumnIndex("name")):"", 
//                            IsExistColumns(cursor.getColumnIndex("number"))?cursor.getString(cursor.getColumnIndex("number")):"" ,
//                            IsExistColumns(cursor.getColumnIndex("jobprofession"))?cursor.getString(cursor.getColumnIndex("jobprofession")):"", 
//                            IsExistColumns(cursor.getColumnIndex("vid"))?cursor.getInt(cursor.getColumnIndex("vid")):0, 
//                            IsExistColumns(cursor.getColumnIndex("shortNumber"))?cursor.getString(cursor.getColumnIndex("shortNumber")):"");
//					Contact.setImagepath(IsExistColumns(cursor.getColumnIndex("imgpath"))?cursor.getString(cursor.getColumnIndex("imgpath")):"");
//					Contact.setShareflg(IsExistColumns(cursor.getColumnIndex("shareflg"))?cursor.getString(cursor.getColumnIndex("shareflg")):"0");
					
					dataList.add(contact);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
				if(cursor != null && !cursor.isClosed()) cursor.close();
				closeDatabase();
		}
		if(dataList == null)
			dataList = new ArrayList<Contact>(1);
//		return decode( dataList );
		return dataList;
	}
	
	/**
	 * 查询组织下员工
	 * @param name 组织名称
	 * @return
	 */
	public synchronized ArrayList<Contact> searchByCompanyName(String name){
		HashMap<String, String> orgidPath = new HashMap<String, String>();
		SQLiteDatabase database=getReadableDatabase();
		ArrayList<Contact> dataList=new ArrayList<Contact>();
		Cursor cursor=database.rawQuery("select DISTINCT cp.[name],cp.[number],cp.[orgid],ct.[serverId] from company_tree as ct inner join company_person as cp on ct.[name] like '%"+name+"%' and ct.[serverId]=cp.[orgid] order by cp.[sid] ASC", null);
		if(cursor!=null){
			while(cursor.moveToNext()){
				Contact cc=new Contact();
				cc.setDisplayName(cursor.getString(0));
				cc.setLinkPhone(cursor.getString(1));
				String orgid = IsExistColumns(2)?cursor.getString(2):"";
				if (!orgid.equals("")) {
					if(orgidPath.containsKey(orgid)) {
						cc.setCompanyPath(orgidPath.get(orgid));
					}
					else {
						cc.setCompanyPath(queryDepartment(Integer.parseInt(orgid), database, "company_tree"));
						orgidPath.put(orgid, cc.getCompanyPath());
					}
				}
				dataList.add(cc);
				String serverId=cursor.getString(3);
				if(!orgid.equals("")&&Integer.valueOf(serverId)!=-1){
					ArrayList<Contact> al=queryByServerId(Integer.valueOf(serverId));
					if(al!=null&&al.size()>0){
						for (Contact cl : al) {
							if(!dataList.contains(cl)){
								dataList.add(cl);
							}
						}
					}
				}
			}
		}
		
		if(cursor!=null) cursor.close();
		closeDatabase();
		
		return dataList;
	}
	
	public ArrayList<Contact> queryByServerId(int serverId){
		SQLiteDatabase database=getReadableDatabase();
		HashMap<String, String> orgidPath = new HashMap<String, String>();
		ArrayList<Contact> dataList=new ArrayList<Contact>();
		Cursor cursor=database.rawQuery("select DISTINCT cp.[name],cp.[number],cp.[orgid] from company_tree as ct inner join company_person as cp on ct.[pid]="+serverId+" and ct.[serverId]=cp.[orgid] order by cp.[sid] asc", null);
		if(cursor!=null){
			while(cursor.moveToNext()){
				Contact cc=new Contact();
				cc.setDisplayName(cursor.getString(0));
				cc.setLinkPhone(cursor.getString(1));
				String orgid = IsExistColumns(2)?cursor.getString(2):"";
				if (!orgid.equals("")) {
					if(orgidPath.containsKey(orgid)) {
						cc.setCompanyPath(orgidPath.get(orgid));
					}
					else {
						cc.setCompanyPath(queryDepartment(Integer.parseInt(orgid), database, "company_tree"));
						orgidPath.put(orgid, cc.getCompanyPath());
					}
				}
				dataList.add(cc);
			}
		}
		
		if(cursor!=null) cursor.close();
		closeDatabase();		
		return dataList;
	}
	

	/**
	 * @param 搜索关键字
	 * @return
	 */
	public synchronized ArrayList<Contact> searchByKey(String table,String key){
		return searchByKey(table,key,0,0,false);
	}
	/**
	 * 根据关键字、起始点、条数 搜索
	 * @param table
	 * @param key
	 * @param start
	 * @param count 如果等于0就代表是全部搜索
	 * @return
	 */
	public synchronized ArrayList<Contact> searchByKey(String table,String key , int start , int count,boolean OnlyNeedInstalled) {
		HashMap<String, String> orgidPath = new HashMap<String, String>();
		SQLiteDatabase database = getReadableDatabase();
		ArrayList<Contact> dataList = new ArrayList<Contact>();
		String treeTable = table == COMPANY_PERSON_DATABASE ? COMPANY_TREE_DATABASE : COMPANY_CLIENT_TREE_TABLE ;
		String where = "";
		String keyEncode = "";
		
		//判断是否只是要搜索出只安装状态的结果
		if(OnlyNeedInstalled){
			
			if ( !StringUtil.isEmpty(key)){
				keyEncode = StringUtil.encode( key );
				if ( StringUtil.isNum(key) ) {
					where = StringUtil.getBuffer().append(" WHERE number like '%").append(key.trim()).append("%'").append(" and install= 1 ").toString();
				}
				else if(keyEncode.length() == 1 && keyEncode.length() == key.length()) {
					where = StringUtil.getBuffer().append(" WHERE firstpy like '").append(key.trim()).append("%'").append(" and install= 1 ").toString();
				}
				else if(keyEncode.length() == key.length()){
					where = StringUtil.getBuffer().append(" WHERE firstpy like '%").append(key).append("%' or  allpy like '%").append(key).append("%'").append(" and install= 1 ").toString();
				} else{
					where = StringUtil.getBuffer().append(" WHERE name like '%").append(key).append("%'").append(" and install= 1 ").toString();
				}
			}
		}else{
			if ( !StringUtil.isEmpty(key)){
				keyEncode = StringUtil.encode( key );
				if ( StringUtil.isNum(key) ) {
					where = StringUtil.getBuffer().append(" WHERE number like '%").append(key.trim()).append("%'").toString();
				}
				else if(keyEncode.length() == 1 && keyEncode.length() == key.length()) {
					where = StringUtil.getBuffer().append(" WHERE firstpy like '").append(key.trim()).append("%'").toString();
				}
				else if(keyEncode.length() == key.length()){
					where = StringUtil.getBuffer().append(" WHERE firstpy like '%").append(key).append("%' or  allpy like '%").append(key).append("%'").toString();
				} else{
					where = StringUtil.getBuffer().append(" WHERE name like '%").append(key).append("%'").toString();
				}
			}
		}
		
		String sql = StringUtil.getBuffer().append("SELECT DISTINCT name, number, orgid, allpy,firstpy,install from ").append(table).append(where).append(" ORDER BY ").append(ORDERBY_person).toString();
		if(count != 0) {
			sql += "limit " + start + "," + count;
		}
		Cursor cursor = null;
		try {
			cursor = database.rawQuery( sql, null );
			if(cursor != null && cursor.moveToFirst()) {
				do{
					Contact contact = new Contact();
					contact.setDisplayName(IsExistColumns(cursor.getColumnIndex("name"))?cursor.getString(cursor.getColumnIndex("name")):"");
					contact.setLinkPhone(IsExistColumns(cursor.getColumnIndex("number"))?cursor.getString(cursor.getColumnIndex("number")):"");
					contact.setNickName(IsExistColumns(cursor.getColumnIndex("nickname"))?cursor.getString(cursor.getColumnIndex("nickname")):"");
					contact.setAllPy(IsExistColumns(cursor.getColumnIndex("allpy"))?cursor.getString(cursor.getColumnIndex("allpy")):"");
					contact.setFirstPy(IsExistColumns(cursor.getColumnIndex("firstpy"))?cursor.getString(cursor.getColumnIndex("firstpy")):"");
					contact.setInstall(IsExistColumns(cursor.getColumnIndex("install"))?cursor.getInt(cursor.getColumnIndex("install")):0);
					
					if(table.equals("client_person")){
						contact.setSourceId("2");
					}else{
						contact.setSourceId("3");
					}
					String orgid = IsExistColumns(cursor.getColumnIndex("orgid"))?cursor.getString(cursor.getColumnIndex("orgid")):"";
					if (!orgid.equals("")) {
						if(orgidPath.containsKey(orgid)) {
							contact.setCompanyPath(orgidPath.get(orgid));
						}
						else {
							contact.setCompanyPath(queryDepartment(Integer.parseInt(orgid), database, treeTable));
							orgidPath.put(orgid, contact.getCompanyPath());
						}
						
					}
					dataList.add(contact);
				}while ( cursor.moveToNext() );
			}
			
		} catch (Exception e){
			e.printStackTrace();
		} finally{
				if(cursor != null && !cursor.isClosed()) cursor.close();
				closeDatabase();
		}
		return dataList;
	}
	
	
	/**
	 * 根据关键字、起始点、条数 搜索
	 * @param table
	 * @param key
	 * @param start
	 * @param count 如果等于0就代表是全部搜索
	 * @return
	 */
	public synchronized ArrayList<Contact> searchByKey(String table,String key,int type , int pageSize , int page) {
		HashMap<String, String> orgidPath = new HashMap<String, String>();
		SQLiteDatabase database = getReadableDatabase();
		ArrayList<Contact> dataList = new ArrayList<Contact>();
		String treeTable = table == COMPANY_PERSON_DATABASE ? COMPANY_TREE_DATABASE : COMPANY_CLIENT_TREE_TABLE ;
		String where = "";
		String keyEncode = "";
		if ( !StringUtil.isEmpty(key)){
			keyEncode = StringUtil.encode( key );
			switch (type) {
			case 1:
				//根据号码模糊查询
				if ( StringUtil.isNum(key) ) {
					where = StringUtil.getBuffer().append(" WHERE number like '%").append(key.trim()).append("%'").toString();
				}
				break;
			case 2:
				//根据名称拼音查询
			 if(keyEncode.length() == 1 && keyEncode.length() == key.length()) {
					int position=StringUtil.charToInt(key.trim().toUpperCase());
					where = StringUtil.getBuffer().append(" WHERE fnamefpynum == ").append(position).toString();
//					where = StringUtil.getBuffer().append(" WHERE fnamefpy like '%").append(key.trim()).append("%'").toString();
				}else if(keyEncode.length() >4&&keyEncode.length() == key.length()){
					
					where = StringUtil.getBuffer().append(" WHERE allpy like '%").append(key).append("%'").toString();
					
				}else if(keyEncode.length() == key.length()){
					
					where = StringUtil.getBuffer().append(" WHERE firstpy like '%").append(key).append("%' or  allpy like '%").append(key).append("%'").toString();
					
				} else{
					where = StringUtil.getBuffer().append(" WHERE name like '%").append(key).append("%'").toString();
				}
				break;
			}
			
//			if ( StringUtil.isNum(key) ) {
////				where = StringUtil.getBuffer().append(" WHERE number like '").append(key.trim()).append("%'").toString();
//			}else if(keyEncode.length() == 1 && keyEncode.length() == key.length()) {
//				
//				int position=com.gmccgz.kakaonew.contact.menhu.util.StringUtil.charToInt(key.trim().toUpperCase());
//				
//				where = StringUtil.getBuffer().append(" WHERE fnamefpynum == ").append(position).toString();
////				where = StringUtil.getBuffer().append(" WHERE fnamefpy like '%").append(key.trim()).append("%'").toString();
//				
//			}else if(keyEncode.length() >4&&keyEncode.length() == key.length()){
//				
//				where = StringUtil.getBuffer().append(" WHERE allpy like '%").append(key).append("%'").toString();
//				
//			}else if(keyEncode.length() == key.length()){
//				
//				where = StringUtil.getBuffer().append(" WHERE firstpy like '%").append(key).append("%' or  allpy like '%").append(key).append("%'").toString();
//				
//			} else{
//				where = StringUtil.getBuffer().append(" WHERE name like '%").append(key).append("%'").toString();
//			}
		}
		String sql = StringUtil.getBuffer().append("SELECT DISTINCT name, number, orgid, allpy,firstpy, fnamefpy, fnamefpynum from ").append(table).append(where).append(" ORDER BY ").append(" sid asc ").toString();
		sql += "limit " + pageSize + " offset " + pageSize*page;
		Cursor cursor = null;
		try {
			cursor = database.rawQuery( sql, null );
			if(cursor != null && cursor.moveToFirst()) {
				do{
					Contact contact = new Contact();
					contact.setDisplayName(IsExistColumns(cursor.getColumnIndex("name"))?cursor.getString(cursor.getColumnIndex("name")):"");
					contact.setLinkPhone(IsExistColumns(cursor.getColumnIndex("number"))?cursor.getString(cursor.getColumnIndex("number")):"");
					contact.setNickName(IsExistColumns(cursor.getColumnIndex("nickname"))?cursor.getString(cursor.getColumnIndex("nickname")):"");
					contact.setAllPy(IsExistColumns(cursor.getColumnIndex("allpy"))?cursor.getString(cursor.getColumnIndex("allpy")):"");
					contact.setFirstPy(IsExistColumns(cursor.getColumnIndex("firstpy"))?cursor.getString(cursor.getColumnIndex("firstpy")):"");
					String orgid = IsExistColumns(cursor.getColumnIndex("orgid"))?cursor.getString(cursor.getColumnIndex("orgid")):"";
					if (!orgid.equals("")) {
						if(orgidPath.containsKey(orgid)) {
							contact.setCompanyPath(orgidPath.get(orgid));
						}
						else {
							contact.setCompanyPath(queryDepartment(Integer.parseInt(orgid), database, treeTable));
							orgidPath.put(orgid, contact.getCompanyPath());
						}
						
					}
					dataList.add(contact);
				}while ( cursor.moveToNext() );
			}
			
		} catch (Exception e){
			e.printStackTrace();
		} finally{
			try{
				
				if(cursor != null && !cursor.isClosed()) cursor.close();
				closeDatabase();
			} catch (Exception e){
				e.printStackTrace();
			}
		}
		return dataList;
	}
	
	
	
	
	
	/**
	 * 根据类型选择搜索
	 * @param table 数据表
	 * @param pynum 第几个字幕
	 * @param pageSize 一页显示多少条数据
	 * @param page 请求第几页
	 * @return
	 */
	public synchronized ArrayList<Contact> searchByKey(String table,int pynum ,int type, int pageSize , int page) {
		HashMap<String, String> orgidPath = new HashMap<String, String>();
		SQLiteDatabase database = getReadableDatabase();
		ArrayList<Contact> dataList = new ArrayList<Contact>();
		String treeTable = table == COMPANY_PERSON_DATABASE ? COMPANY_TREE_DATABASE : COMPANY_CLIENT_TREE_TABLE ;
		String where = "";
		String keyEncode = "";
		where = StringUtil.getBuffer().append(" WHERE fnamefpynum == ").append(pynum).toString();
		String sql = StringUtil.getBuffer().append("SELECT DISTINCT name, number, orgid, allpy,firstpy, fnamefpy, fnamefpynum from ").append(table).append(where).append(" ORDER BY ").append(" sid asc ").toString();
		if(pageSize != 0) {
			sql += "limit " + pageSize + " offset " + pageSize*page;
		}
		Cursor cursor = null;
		try {
			cursor = database.rawQuery( sql, null );
			if(cursor != null && cursor.moveToFirst()) {
				do{
					Contact contact = new Contact();
					contact.setDisplayName(IsExistColumns(cursor.getColumnIndex("name"))?cursor.getString(cursor.getColumnIndex("name")):"");
					contact.setLinkPhone(IsExistColumns(cursor.getColumnIndex("number"))?cursor.getString(cursor.getColumnIndex("number")):"");
					contact.setNickName(IsExistColumns(cursor.getColumnIndex("nickname"))?cursor.getString(cursor.getColumnIndex("nickname")):"");
					contact.setAllPy(IsExistColumns(cursor.getColumnIndex("allpy"))?cursor.getString(cursor.getColumnIndex("allpy")):"");
					contact.setFirstPy(IsExistColumns(cursor.getColumnIndex("firstpy"))?cursor.getString(cursor.getColumnIndex("firstpy")):"");
					contact.setFnamefpy(IsExistColumns(cursor.getColumnIndex("fnamefpy"))?cursor.getString(cursor.getColumnIndex("fnamefpy")):"");
					contact.setSort(IsExistColumns(cursor.getColumnIndex("fnamefpynum"))?Integer.valueOf(cursor.getString(cursor.getColumnIndex("fnamefpynum"))):1);
					String orgid = IsExistColumns(cursor.getColumnIndex("orgid"))?cursor.getString(cursor.getColumnIndex("orgid")):"";
					if (!orgid.equals("")) {
						if(orgidPath.containsKey(orgid)) {
							contact.setCompanyPath(orgidPath.get(orgid));
						}
						else {
							contact.setCompanyPath(queryDepartment(Integer.parseInt(orgid), database, treeTable));
							orgidPath.put(orgid, contact.getCompanyPath());
						}
						
					}
					dataList.add(contact);
				}while ( cursor.moveToNext() );
			}
			
		} catch (Exception e){
			e.printStackTrace();
		} finally{
			try{
				
				if(cursor != null && !cursor.isClosed()) cursor.close();
				closeDatabase();
			} catch (Exception e){
				e.printStackTrace();
			}
		}
		return dataList;
	}
	
	// 查询集团组织树
	public ArrayList<Org> queryAllTree(int treeId, String table) {
		ArrayList<Org> treeList = new ArrayList<Org>();
		SQLiteDatabase database = getReadableDatabase();
		if(database == null)
			return treeList;
		Cursor cursor = null;
		try {
			String where = new StringBuffer().append(" pid = ").append(treeId).toString();
			//同事数据没有分享标记
			if(table.equals(COMPANY_TREE_DATABASE)) {
				cursor = database.query(true, table, NOSHARE_SELECTCOLUMNS, where, null, null, null, ORDERBY_tree, null);
			}
			else {
				cursor = database.query(true, table, SELECTCOLUMNS, where, null, null, null, ORDERBY_tree, null);
			}
			
			if ( cursor != null ) {
				while ( cursor.moveToNext() ) {
					if(table.equals(COMPANY_TREE_DATABASE)) {
					treeList.add( new Org(cursor.getInt(0), cursor.getString(2), 
							"", (cursor.getInt(3) == 0 ? false : true), (cursor.getInt(4) == 0 ? false : true),
							cursor.getInt(1), cursor.getInt(5), false ,"") );
					}
					else {
						treeList.add( new Org(cursor.getInt(0), cursor.getString(2), 
								"", (cursor.getInt(3) == 0 ? false : true), (cursor.getInt(4) == 0 ? false : true),
								cursor.getInt(1), cursor.getInt(5), false ,cursor.getString(6)) );
					}
				}
			}
		}
		catch(Exception e) {
			e.printStackTrace();
		}finally {
			
			if(cursor != null) cursor.close();
			closeDatabase();
		}
		return treeList;
	}
	
	// 查询集团组织树
	public ArrayList<Org> queryTop(String table) {
		SQLiteDatabase database = getReadableDatabase();
		ArrayList<Org> treeList = new ArrayList<Org>(1);
		if(database == null)
			return treeList;
		//添加去重
		String sql = null;
		//modify miaozz 因为客户表可添加多个公司level为1，故在上层再添加一个level为0
//		if(table.equals(ContactDbHelper.COMPANY_CLIENT_TREE_TABLE)) {
//			sql = StringUtil.getBuffer().append("SELECT DISTINCT ").append(COLUMNS).append(" FROM ")
//					.append(table).append(" WHERE level=0 ORDER BY ")
//					.append(ORDERBY_tree).toString();
//		} else {
			sql = StringUtil.getBuffer().append("SELECT DISTINCT ").append(COLUMNS).append(" FROM ")
					.append(table).append(" WHERE level=1 ORDER BY ")
					.append(ORDERBY_tree).toString();
//		}
		Cursor cursor = null;
		try {
			cursor = database.rawQuery(sql, null);
			if (cursor != null) {
				while (cursor.moveToNext())
				{
					Org tree = new Org(cursor.getInt(0), cursor.getString(2), "", (cursor.getInt(3) == 0 ? false : true),
							(cursor.getInt(4) == 0 ? false : true), cursor.getInt(1), cursor.getInt(5), false);
					treeList.add(tree);
				}
			}
		}
		catch(Exception e) {
			e.printStackTrace();
		}finally {
			
			
			if(cursor != null) cursor.close();
			closeDatabase();
		}
		return treeList;
	}
	// 查询平安广州助手
	public synchronized ArrayList<Contact> queryAll( int orgId, String table , String columns,boolean OnlyNeedInstalled) {
//		if(Const.OFFICEASSISTANT == ServerUrl.ProjectNO){
//			return queryPersonByPermissionForSuZhou(orgId, "");
//		}else{
		SQLiteDatabase database = getReadableDatabase();
			Map<String,String> map=new HashMap<String,String>();
			if(database == null)
				return new ArrayList<Contact>(1);
	
			ArrayList<Contact> dataList = new ArrayList<Contact>();
			String sql =null;
			if(OnlyNeedInstalled){
				sql = new StringBuffer().append("SELECT DISTINCT ").append(columns).append(" FROM ").append(table).append(" WHERE orgid = ").append(orgId).append(" and install= 1 ").append(" and jobprofession <> ").append(" '集团管理员'").append(" ORDER BY ").append(ORDERBY_person).toString();
			}else{
				sql = new StringBuffer().append("SELECT DISTINCT ").append(columns).append(" FROM ").append(table).append(" WHERE orgid = ").append(orgId).append(" and jobprofession <> ").append(" '集团管理员'").append(" ORDER BY ").append(ORDERBY_person).toString();
			}
			
			Cursor cursor = null;
			try {
				cursor = database.rawQuery(sql, null);
				if (cursor != null && cursor.getCount() > 0) {
					while (cursor.moveToNext()) {
//						Contact Contact = new Contact(IsExistColumns(cursor.getColumnIndex("name"))?cursor.getString(cursor.getColumnIndex("name")):"", 
//								                                           IsExistColumns(cursor.getColumnIndex("number"))?cursor.getString(cursor.getColumnIndex("number")):"" ,
//								                                           IsExistColumns(cursor.getColumnIndex("jobprofession"))?cursor.getString(cursor.getColumnIndex("jobprofession")):"", 
//								                                           IsExistColumns(cursor.getColumnIndex("vid"))?cursor.getInt(cursor.getColumnIndex("vid")):0, 
//								                                           IsExistColumns(cursor.getColumnIndex("shortNumber"))?cursor.getString(cursor.getColumnIndex("shortNumber")):"",
//								                                           IsExistColumns(cursor.getColumnIndex("orgid"))?cursor.getInt(cursor.getColumnIndex("orgid")):-1,
//								                                           IsExistColumns(cursor.getColumnIndex("nickname"))?cursor.getString(cursor.getColumnIndex("nickname")):"");
						Contact	contact = new Contact();
						contact.setDisplayName(cursor.getString(cursor.getColumnIndex("name")));
						contact.setLinkPhone(cursor.getString(cursor.getColumnIndex("number")));
						contact.setJobprofession(cursor.getString(cursor.getColumnIndex("jobprofession")));
						contact.setOrgId(cursor.getInt(cursor.getColumnIndex("orgid")));
//						Contact.setImagepath(IsExistColumns(cursor.getColumnIndex("imgpath"))?cursor.getString(cursor.getColumnIndex("imgpath")):"");
//						Contact.setShareflg(IsExistColumns(cursor.getColumnIndex("shareflg"))?cursor.getString(cursor.getColumnIndex("shareflg")):"0");
						
						contact.setAllPy(IsExistColumns(cursor.getColumnIndex("allpy"))?cursor.getString(cursor.getColumnIndex("allpy")):"");
						contact.setFirstPy(IsExistColumns(cursor.getColumnIndex("firstpy"))?cursor.getString(cursor.getColumnIndex("firstpy")):"");
						contact.setInstall(IsExistColumns(cursor.getColumnIndex("install"))?cursor.getInt(cursor.getColumnIndex("install")):0);
						
						if(table.equals("client_person")){
							contact.setSourceId("2");
						}else{
							contact.setSourceId("3");
						}
						
						if((IsExistColumns(cursor.getColumnIndex("orgid"))?cursor.getInt(cursor.getColumnIndex("orgid")):-1)!=-1){
							String orgid=cursor.getString(cursor.getColumnIndex("orgid"));
							if(map.containsKey(orgid)){
								contact.setCompanyPath(map.get(orgid));
							}else{
								String str=null;
								if(table.equals("client_person")){
									str="client_tree";
								}else{
									str="company_tree";
								}
								String cm=queryDepartment(Integer.valueOf(orgid), getReadableDatabase(), str);
								map.put(orgid, cm);
								contact.setCompanyPath(cm);
							}
						}
						dataList.add(contact);
					}
				}
			} catch(Exception e) {
				e.printStackTrace();
			} finally {
				if(cursor != null) cursor.close();
				closeDatabase();
			}
			return dataList;
//		}
	}
	
	// 查询平安广州助手
		public synchronized ArrayList<Contact> queryAll( int orgId, String table ) {
//			if(Const.OFFICEASSISTANT == ServerUrl.ProjectNO){
//				return queryPersonByPermissionForSuZhou(orgId, "");
//			}else{
			SQLiteDatabase database = getReadableDatabase();
				Map<String,String> map=new HashMap<String,String>();
				if(database == null)
					return new ArrayList<Contact>(1);
		
				ArrayList<Contact> dataList = new ArrayList<Contact>();
				
				String sql = new StringBuffer().append("SELECT DISTINCT ").append("name,number,jobprofession,imgpath,orgid,firstpy,allpy,install").append(" FROM ").append(table).append(" WHERE orgid = ").append(orgId).append(" and jobprofession <> ").append(" '集团管理员'").append(" ORDER BY ").append("fnamefpy asc , fnamefpynum asc").toString();
				Cursor cursor = null;
				try {
					cursor = database.rawQuery(sql, null);
					if (cursor != null && cursor.getCount() > 0) {
						String sourceId="";
						if(table.equals("client_person")){
							sourceId="2";
						}else{
							sourceId="3";
							
						}
						while (cursor.moveToNext()) {
//							Contact Contact = new Contact(IsExistColumns(cursor.getColumnIndex("name"))?cursor.getString(cursor.getColumnIndex("name")):"", 
//									                                           IsExistColumns(cursor.getColumnIndex("number"))?cursor.getString(cursor.getColumnIndex("number")):"" ,
//									                                           IsExistColumns(cursor.getColumnIndex("jobprofession"))?cursor.getString(cursor.getColumnIndex("jobprofession")):"", 
//									                                           IsExistColumns(cursor.getColumnIndex("vid"))?cursor.getInt(cursor.getColumnIndex("vid")):0, 
//									                                           IsExistColumns(cursor.getColumnIndex("shortNumber"))?cursor.getString(cursor.getColumnIndex("shortNumber")):"",
//									                                           IsExistColumns(cursor.getColumnIndex("orgid"))?cursor.getInt(cursor.getColumnIndex("orgid")):-1,
//									                                           IsExistColumns(cursor.getColumnIndex("nickname"))?cursor.getString(cursor.getColumnIndex("nickname")):"");
							Contact contact = new Contact();
//							Contact.setImagepath(IsExistColumns(cursor.getColumnIndex("imgpath"))?cursor.getString(cursor.getColumnIndex("imgpath")):"");
//							Contact.setShareflg(IsExistColumns(cursor.getColumnIndex("shareflg"))?cursor.getString(cursor.getColumnIndex("shareflg")):"0");
							
							contact.setDisplayName(cursor.getString(cursor.getColumnIndex("name")));
							contact.setLinkPhone(cursor.getString(cursor.getColumnIndex("number")));
							contact.setJobprofession(cursor.getString(cursor.getColumnIndex("jobprofession")));
							contact.setOrgId(cursor.getInt(cursor.getColumnIndex("orgid")));
							
							contact.setAllPy(IsExistColumns(cursor.getColumnIndex("allpy"))?cursor.getString(cursor.getColumnIndex("allpy")):"");
							contact.setFirstPy(IsExistColumns(cursor.getColumnIndex("firstpy"))?cursor.getString(cursor.getColumnIndex("firstpy")):"");
							contact.setInstall(IsExistColumns(cursor.getColumnIndex("install"))?cursor.getInt(cursor.getColumnIndex("install")):0);
							
							contact.setSourceId(sourceId);
							
							if((IsExistColumns(cursor.getColumnIndex("orgid"))?cursor.getInt(cursor.getColumnIndex("orgid")):-1)!=-1){
								String orgid=cursor.getString(cursor.getColumnIndex("orgid"));
								if(map.containsKey(orgid)){
									contact.setCompanyPath(map.get(orgid));
								}else{
									String str=null;
									if(table.equals("client_person")){
										str="client_tree";
									}else{
										str="company_tree";
									}
									String cm=queryDepartment(Integer.valueOf(orgid), getReadableDatabase(), str);
									map.put(orgid, cm);
									contact.setCompanyPath(cm);
								}
							}
							dataList.add(contact);
						}
					}
				} catch(Exception e) {
					e.printStackTrace();
				} finally {
					if(cursor != null) cursor.close();
					closeDatabase();
				}
				return dataList;
//			}
		}
//	
	/**
	 *  查询个人信息
	 * @param String number
	 * @return Contact
	 */
	public Contact queryUserInfo( String number , String table) {
		if (StringUtil.isEmpty( number ))
			return null;
		SQLiteDatabase database = getReadableDatabase();
		if(database == null)
			return null;
		Cursor cursor = null;
		Contact contact = null;
		try {
			//查询部门
			if (table.equals(COMPANY_PERSON_DATABASE)) {
				cursor = database.query(table, selectionsforcompany, "number='" + number + "'", null, null, null, null);
					if ( cursor != null && cursor.moveToFirst() ) {
						contact = new Contact();
						contact.setDisplayName( cursor.getString( 0 ) );
						contact.setJobprofession(cursor.getString(1));
						contact.setLinkPhone( cursor.getString(2));
						contact.setOrgId(cursor.getInt(3));
						contact.setCompanyPath(queryDepartment(contact.getOrgId(), database, COMPANY_TREE_DATABASE));
					}
			} else {
				cursor = database.query(table, selectionsforclient, "number='" + number + "'", null, null, null, null);
					if ( cursor != null && cursor.moveToFirst() ) {
						contact = new Contact();
						contact.setUserid( cursor.getString( 0 ) );
						contact.setDisplayName( cursor.getString( 1 ) );
						contact.setJobprofession(cursor.getString(2));
						contact.setLinkPhone( cursor.getString(3));
						contact.setOrgId(cursor.getInt(4));
						contact.setNickName(cursor.getString(5));
						contact.setCompanyPath(queryDepartment(contact.getOrgId(), database, COMPANY_CLIENT_TREE_TABLE));
					}
			}
			return contact;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(cursor != null) 	cursor.close();
			closeDatabase();
		}
		return null;
	}
	
	/**
	 *  查询项目组成员
	 * @param String number
	 * @return Contact
	 */
	public Contact queryUserInfo(Context mContext, String number) {
		if (StringUtil.isEmpty( number ))
			return null;
		SQLiteDatabase database = getReadableDatabase();
		if(database == null)
			return null;
		Cursor cursor = null;
		Contact contact = null;
		try {
			cursor = database.query(COMPANY_PERSON_DATABASE, selectionsforcompany, "number='" + number + "'", null, null, null, null);
			if (cursor != null && cursor.moveToFirst() ) {
				contact = new Contact();
				contact.setDisplayName( cursor.getString( 0 ) );
				contact.setJobprofession(cursor.getString(1));
				contact.setLinkPhone( cursor.getString(2));
				contact.setOrgId(cursor.getInt(3));
				contact.setUserid(cursor.getInt(4) + "");
				contact.setSourceId("3");
				contact.setInstall(IsExistColumns(cursor.getColumnIndex("install"))?cursor.getInt(cursor.getColumnIndex("install")):0);
				contact.setCompanyPath(queryDepartment(contact.getOrgId(), database, COMPANY_TREE_DATABASE));
			} else {
				cursor = database.query(COMPANY_CLIENT_PERSON_TABLE, selectionsforclient, "number='" + number + "'", null, null, null, null);
				if (cursor != null && cursor.moveToFirst()) {
					contact = new Contact();
					contact.setDisplayName( cursor.getString( 1 ) );
					contact.setJobprofession(cursor.getString(2));
					contact.setLinkPhone( cursor.getString(3));
					contact.setOrgId(cursor.getInt(4));
					contact.setNickName(cursor.getString(5));
					contact.setSourceId("2");
					contact.setInstall(IsExistColumns(cursor.getColumnIndex("install"))?cursor.getInt(cursor.getColumnIndex("install")):0);
					contact.setCompanyPath(queryDepartment(contact.getOrgId(), database, COMPANY_CLIENT_TREE_TABLE));
				}else{
					contact=APPContactDao.getContactByNumbersOrName(mContext, number);
				}
			}
			return contact;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(cursor != null) cursor.close();
			closeDatabase();
		}
		return null;
	}
	
	/**
	 * 查询部门
	 * @param orgid 部门ID
	 * @param database 数据库
	 * @param tableTree 查询表名
	 * @author luozb
	 */
	private String queryDepartment(int orgid, SQLiteDatabase database, String tableTree) {
		StringBuilder companyPath = new StringBuilder();
		queryDepartment(companyPath, orgid, database, tableTree);
		if(companyPath.length() > 0) {
//			return companyPath.deleteCharAt(companyPath.length() - 1)
//					.toString();
			return companyPath.toString();
		}
		return null;
	}
	
	/**
	 * 查询部门
	 * @author luozb
	 */
	private void queryDepartment(StringBuilder companyPath, int orgid, SQLiteDatabase database, String databaseName) {
		Cursor cursor = database.query(databaseName, new String[]{"pid","name","companypath"}, "serverId='" + orgid + "'", null, null, null, null);
		if(cursor != null && cursor.getCount() > 0) {
			//miaozz modify 去除重复组织显示
			if(cursor.moveToNext()) {
//				orgid = cursor.getInt(0);
//				companyPath.insert(0, cursor.getString(1) + "/");
				companyPath.append(cursor.getString(cursor.getColumnIndex("companypath")));
				return ;
			}
		}
		if(orgid != -1 && cursor.getCount() > 0) {
			queryDepartment(companyPath, orgid, database, databaseName);
		}
		
		if(cursor!=null) {
			cursor.close();
		}
	}
	
	/**
	 * 执行SQL语句
	 * @param _sql
	 * @return
	 */
	public boolean executeSQL(String _sql) {
		SQLiteDatabase database = getReadableDatabase();
		if(null == database) {
			return false;
		}
		//Log.e("客户通讯录sql---", "======================="+_sql);
		_sql = _sql.replaceAll("_tshh_", "\n");
		database.execSQL(_sql);
		closeDatabase();
		
		return true;
	}
	
	public boolean update(ContentValues values) {
		SQLiteDatabase database = getReadableDatabase();
		if(database == null) {
			return false;
		}
		database.update(AYSN_INFO_TABLE, values, "type="+values.get("type"), null);
		closeDatabase();
		return true;
	}
	
	private boolean IsExistColumns(int index) {
		return index!=-1;
	}

	/**
	 * 添加一个根节点数据
	 */
	public void insert2ClientTree() {
		SQLiteDatabase database =getReadableDatabase();
		if(null == database) {
			return ;
		}
		ContentValues cv = new ContentValues();
		cv.put("serverid", "-1");
		cv.put("pid", "-2");
		cv.put("name", "客户");
		cv.put("hasParent", "1");
		cv.put("hasChild", "1");
		cv.put("level", "0");
		cv.put("oid", "1000");
		database.insert(COMPANY_CLIENT_TREE_TABLE, null, cv);
		closeDatabase();
	}
	
	
	  public boolean executesql(List<String> sqlList){
		  
		  SQLiteDatabase db = getReadableDatabase();
			if(null == db) {
				return false;
			}
			if(sqlList==null || sqlList.size()==0){
				return false;
			}
			db.beginTransaction();  //手动设置开始事务
		      try{
		          //批量处理操作
		          for(String sql :sqlList){
		        	  try {
		        		sql = sql.replaceAll("_tshh_", "\n");
						db.execSQL(sql);
					} catch (Exception e) {
						e.printStackTrace();
						continue;
					}
		          }
		          db.setTransactionSuccessful(); //设置事务处理成功，不设置会自动回滚不提交
		          return true;
		         }catch(Exception e){
		        	 e.printStackTrace();
		          return false;
		         }finally{
		             db.endTransaction(); //处理完成
		             closeDatabase();

		         }
		  }

	  /**
	   * 修改同事company_tree表 组织路径
	   */
	  public synchronized void createComPanyTreePath(){
		    List<String> sqlist=new ArrayList<String>();
		    sqlist.add("drop view if exists view_company_tree");
	        sqlist.add("update company_tree set companypath=''");
	        sqlist.add("create view view_company_tree as select * from company_tree");
	        sqlist.add("update company_tree set companypath=name where pid=-1");
	        sqlist.add("update company_tree set companypath=((select companypath from view_company_tree where company_tree.pid=view_company_tree.serverId) || '/'|| name) where pid<>-1");
	        executesql(sqlist);
	  }
	  
	  /**
	   * 修改客户client_tree表 组织路径
	   */
	  public void createClientTreePath(){
		    List<String> sqlist=new ArrayList<String>();
	        sqlist.add("update client_tree set companypath=''");
	        sqlist.add("create view view_client_tree as select * from client_tree");
	        sqlist.add("update client_tree set companypath=name where pid=-1");
	        sqlist.add("update client_tree set companypath=((select companypath from view_client_tree where client_tree.pid=view_client_tree.serverId) || '/'|| name) where pid<>-1");
	        executesql(sqlist);
	  }
	  /**
	   * 同事的组织路径是否有变化
	   * @param sql
	   * @return
	   */
	 public boolean hasChangeCompanyTree(String sql){
		 boolean hasChange=false;
		 if(sql.contains("company_tree")||sql.contains("company_person") || sql.contains("tb_phrase") || sql.contains("tb_phrase_type")){
			 return true;
		 }
		 return hasChange;
		 
	 }
	 /**
	  * 客户的组织路径是否有变化
	  * @param sql
	  * @return
	  */
	 public boolean hasChangeClientTree(String sql){
		 boolean hasChange=false;
		 if(sql.contains("client_tree") ||sql.contains("client_person")){
			 return true;
		 }
		 return hasChange;
		 
	 }

//	 /**
//	  * 数据是否存在
//	  * @return
//	  */
	 public boolean ExistCompanyData(){ 
	     SQLiteDatabase database=getReadableDatabase();
	 	if(database == null)
			return false;
		 Cursor cursor=null;
         try {
			String sql="select _id from company_tree";
			 cursor=database.rawQuery(sql, null);
			 if(cursor!=null && cursor.getCount()>0){
				 return true;
			 }
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			
			if(cursor!=null){
				cursor.close();
			}
			closeDatabase();
		}
		 return false;
	 }
	/**
	 * 查询客户组织表中的companycode
	 * @return
	 */
	 public String findClintCompanyCodes(){
		 SQLiteDatabase database=getReadableDatabase();
		
		 String sql="select companycode from client_tree where companycode>=0";
		 Cursor cursor=null;
		 JSONObject  jsonobj=new JSONObject();
		 try {
			 cursor=database.rawQuery(sql, null);
			 if(cursor!=null && cursor.getCount()>0){
				 JSONArray jsonArray=new JSONArray();
				 while (cursor.moveToNext()) {
					 jsonArray.put(cursor.getString(0));
				}
				 jsonobj.put("company_codes", jsonArray);
			 }else{
				 return "";
			 }
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		}finally{
			
			if(cursor!=null){
				cursor.close();
			}
			closeDatabase();
		}
		return jsonobj.toString();
		 
	 }
	 
	 /**
	  * 
	 * @Title: updateInstalls
	 * @Description:更新安装状态 
	 * @author liyezhen
	 * @param phones
	 * @param install
	 * @throws
	  */
	 public void updateInstalls(ArrayList<String> phones,int install){
		 if(phones == null){
		 }
		 StringBuffer mobiles = new StringBuffer();
		 int i = 0; 
		 for(String phone:phones){
			 if(i != phones.size()-1){
				 mobiles.append(phone).append(",");
			 }else{
				 mobiles.append(phone);
			 }
		 }
		 String sql = "update company_person set install="+install+" where number in("+mobiles+")";
		 SQLiteDatabase db = getReadableDatabase();
		 if(null != db) {
			 db.execSQL(sql);
			 closeDatabase();
		 }
	 }
	 
	 /**
	  * 
	 * @Title: countDepartChildNums
	 * @Description: 查询部门子结点个数
	 * @author liyezhen
	 * @param departid
	 * @return
	 * @throws
	  */
	 public int countDepartChildNums(String departid){
		 SQLiteDatabase database = getReadableDatabase();
		 String usersql = "select count(*) from "+ContactDbHelper.COMPANY_PERSON_DATABASE+" where orgid=?";
		 int countdepart = 0;
		 int countuser = 0;
		 Cursor userc = database.rawQuery(usersql, new String[]{departid});
		 if(userc != null && userc.getCount()>0){
			 if(userc.moveToNext()){
				 countuser = userc.getInt(0);
			 }
			 userc.close();
		 }
		 closeDatabase();
		 return countdepart+countuser;
	 }
}
